CREATE TABLE RPS_POLICY
	(
	POLICY_ID    INT NOT NULL,
	POLICY_DESC  NVARCHAR (50) NOT NULL,
	CONSTRAINT PK_RPS_POLICY PRIMARY KEY (POLICY_ID)
	)
GO

INSERT INTO RPS_POLICY VALUES (0, 'Read Only')
GO
INSERT INTO RPS_POLICY VALUES (1, 'Add Task')
GO
INSERT INTO RPS_POLICY VALUES (2, 'Remove Task')
GO
INSERT INTO RPS_POLICY VALUES (3, 'Edit Task')
GO
INSERT INTO RPS_POLICY VALUES (4, 'Copy Task')
GO
INSERT INTO RPS_POLICY VALUES (5, 'Paste Task')
GO
INSERT INTO RPS_POLICY VALUES (6, 'Move Task')
GO
INSERT INTO RPS_POLICY VALUES (7, 'Export Tasks')
GO
INSERT INTO RPS_POLICY VALUES (8, 'Import Tasks')
GO
INSERT INTO RPS_POLICY VALUES (9, 'Start Task')
GO
INSERT INTO RPS_POLICY VALUES (10, 'Resume Task')
GO
INSERT INTO RPS_POLICY VALUES (11, 'Stop Task')
GO
INSERT INTO RPS_POLICY VALUES (12, 'Abort Task')
GO
INSERT INTO RPS_POLICY VALUES (13, 'Activate Task')
GO
INSERT INTO RPS_POLICY VALUES (14, 'Deactivate Task')
GO
INSERT INTO RPS_POLICY VALUES (15, 'Edit Holiday Calendar')
GO
INSERT INTO RPS_POLICY VALUES (16, 'Manager Users')
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASK_HOLIDAY_CALENDAR
	(
	CAL_ID			INT NOT NULL,
	CAL_NAME   		NVARCHAR(50) NOT NULL,
	CAL_DESC   		NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	[VERSION]		ROWVERSION NOT NULL,
	CONSTRAINT PK_RPS_TASK_HOLIDAY_CALENDAR PRIMARY KEY (CAL_ID)
	)
GO

CREATE INDEX IX_RPS_TASK_HOLIDAY_CALENDAR
	ON RPS_TASK_HOLIDAY_CALENDAR (CAL_NAME)
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASK_HOLIDAY_CALENDAR_AUDIT
	(
	CAL_ID			INT NOT NULL,
	CAL_NAME   		NVARCHAR(50) NOT NULL,
	CAL_DESC   		NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	[VERSION]		INT NOT NULL,
	CONSTRAINT PK_RPS_TASK_HOLIDAY_CALENDAR_AUDIT PRIMARY KEY (CAL_ID, [VERSION])
	)
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASK_HOLIDAY_CALENDAR_DATES
	(
	CAL_ID			INT NOT NULL,
	CAL_DATE   		DATETIME NOT NULL,
	CAL_DATE_DESC	NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	[VERSION]		ROWVERSION NOT NULL,
	CONSTRAINT PK_RPS_TASK_HOLIDAY_CALENDAR_DATES PRIMARY KEY (CAL_ID, CAL_DATE)
	)
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASK_HOLIDAY_CALENDAR_DATES_AUDIT
	(
	CAL_ID			INT NOT NULL,
	CAL_DATE   		DATETIME NOT NULL,
	CAL_DATE_DESC	NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	[VERSION]		INT NOT NULL,
	CONSTRAINT PK_RPS_TASK_HOLIDAY_CALENDAR_DATES_AUDIT PRIMARY KEY (CAL_ID, CAL_DATE, [VERSION])
	)
GO

----------------------------------------------------------------------------

INSERT INTO RPS_TASK_HOLIDAY_CALENDAR (CAL_NAME, CAL_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE) 
VALUES ('<NULL>', 'Empty Calendar', 'SYSTEM', '10/25/2013', 'SYSTEM', '10/25/2013')

----------------------------------------------------------------------------

CREATE TABLE RPS_TASK_SCHEDULE_TYPES
	(
	TASK_SCHEDULE_TYPE_ID    INT NOT NULL,
	TASK_SCHEDULE_TYPE_DESC  NVARCHAR (50) NOT NULL,
	CONSTRAINT PK_RPS_TASK_SCHEDULE_TYPES PRIMARY KEY (TASK_SCHEDULE_TYPE_ID)
	)
GO

INSERT INTO RPS_TASK_SCHEDULE_TYPES VALUES (1, 'DAILY')
GO
INSERT INTO RPS_TASK_SCHEDULE_TYPES VALUES (2, 'WEEKLY')
GO
INSERT INTO RPS_TASK_SCHEDULE_TYPES VALUES (3, 'MONTHLY')
GO
INSERT INTO RPS_TASK_SCHEDULE_TYPES VALUES (4, 'ONCE')
GO

----------------------------------------------------------------------------

CREATE TABLE RPS_TASK_STATUS
	(
	TASK_STATUS_ID    INT NOT NULL,
	TASK_STATUS_DESC  NVARCHAR (50) NOT NULL,
	CONSTRAINT PK_RPS_TASK_STATUS PRIMARY KEY (TASK_STATUS_ID)
	)
GO

INSERT INTO RPS_TASK_STATUS VALUES (0, 'INVALID')
GO
INSERT INTO RPS_TASK_STATUS VALUES (1, 'INACTIVE')
GO
INSERT INTO RPS_TASK_STATUS VALUES (2, 'ACTIVE')
GO
INSERT INTO RPS_TASK_STATUS VALUES (3, 'RUNNING')
GO
INSERT INTO RPS_TASK_STATUS VALUES (4, 'SUCCESS')
GO
INSERT INTO RPS_TASK_STATUS VALUES (5, 'FAILED')
GO
INSERT INTO RPS_TASK_STATUS VALUES (6, 'STOPPED')
GO
INSERT INTO RPS_TASK_STATUS VALUES (7, 'ABORTED')
GO
INSERT INTO RPS_TASK_STATUS VALUES (8, 'TIMEDOUT')
GO
INSERT INTO RPS_TASK_STATUS VALUES (9, 'SCHEDULED')
GO

----------------------------------------------------------------------------

CREATE TABLE RPS_USERS
(
    ID INT NOT NULL,
    NAME NVARCHAR(50) NOT NULL,
    FULL_NAME NVARCHAR(250) NOT NULL,
    PASSWORD NVARCHAR(50) NULL,
	ENABLED BIT NOT NULL DEFAULT(1),
    CONSTRAINT PK_RPS_USERS PRIMARY KEY (ID),
    CONSTRAINT UQ_RPS_USERS UNIQUE (NAME)
)
GO


INSERT INTO RPS_USERS (NAME, FULL_NAME, PASSWORD, ENABLED) VALUES ('ADMIN', 'Administrator', NULL, 1)

----------------------------------------------------------------------------

CREATE TABLE RPS_ROLES
(
    ID INT NOT NULL,
    NAME NVARCHAR(50) NOT NULL,
    COMMENT NVARCHAR(2500) NULL,
	ENABLED BIT NOT NULL DEFAULT(1),

    CONSTRAINT PK_RPS_ROLES PRIMARY KEY (ID),
    CONSTRAINT UQ_RPS_ROLES UNIQUE (NAME)
)
GO

CREATE TABLE RPS_USER_ROLES
(
    USER_ID int NOT NULL,
    ROLE_ID int NOT NULL,

    CONSTRAINT PK_RPS_USER_ROLES PRIMARY KEY (USER_ID, ROLE_ID),
    CONSTRAINT UQ_RPS_REVERSEPK UNIQUE (ROLE_ID, USER_ID)
)

CREATE TABLE RPS_ROLE_POLICY
(
    ROLE_ID int NOT NULL,
    POLICY_ID int NOT NULL,

    CONSTRAINT PK_RPS_ROLE_POLICY PRIMARY KEY (ROLE_ID, POLICY_ID)
)

----------------------------------------------------------------

INSERT INTO RPS_USER_ROLES (USER_ID, ROLE_ID)
VALUES (1, 1)
GO

INSERT INTO RPS_ROLES (NAME, ENABLED)
VALUES ('ADMIN', 1)
GO

INSERT INTO RPS_ROLES (NAME, ENABLED)
VALUES ('READ_ONLY', 1)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 1)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 2)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 3)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 4)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 5)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 6)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 7)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 8)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 9)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 10)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 11)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 13)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 14)
GO

INSERT INTO RPS_ROLE_POLICY (ROLE_ID, POLICY_ID)
VALUES (1, 15)
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASK_GRPS
	(
	TASK_GRP_ID   INT NOT NULL,
	TASK_GRP_NAME NVARCHAR (50) NOT NULL,
	TASK_GRP_DESC NVARCHAR (2500),
	DELETED	      BIT NOT NULL,
	CREATED_BY	  NVARCHAR(100) NOT NULL,
	CREATED_DATE  DATETIME NOT NULL,
	MODIFIED_BY	  NVARCHAR(100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	CONSTRAINT PK_RPS_TASK_GRPS PRIMARY KEY (TASK_GRP_ID)
	)
GO

CREATE INDEX IX_RPS_TASK_GRPS
	ON RPS_TASK_GRPS (TASK_GRP_NAME)
GO
----------------------------------------------------------------

CREATE TABLE RPS_TASK_GRPS_AUDIT
	(
	TASK_GRP_ID   INT NOT NULL,
	TASK_GRP_NAME NVARCHAR (50) NOT NULL,
	TASK_GRP_DESC NVARCHAR (2500),
	DELETED       BIT NOT NULL,
	CREATED_BY	  NVARCHAR(100) NOT NULL,
	CREATED_DATE  DATETIME NOT NULL,
	MODIFIED_BY   NVARCHAR (100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	CONSTRAINT PK_RPS_TASK_GRPS_AUDIT PRIMARY KEY (TASK_GRP_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASKS
	(
	TASK_ID				INT NOT NULL,
	TASK_NAME			NVARCHAR (50) NOT NULL,
	WORKFLOW_NAME		NVARCHAR (50) NOT NULL,
	TASK_DESC			NVARCHAR (2500),
	TASK_GRP_ID			NVARCHAR (50) NOT NULL,
	CMD_PARAMETERS		NVARCHAR(2500),
	NO_OF_RETRY			INT,
	SLEEP_BETWEEN_RETRY INT,
	[TIMEOUT]			INT,
	DELETED	  			BIT NOT NULL,
	MACHINE_NAME		NVARCHAR(50) NOT NULL,
	CREATED_BY			NVARCHAR(100) NOT NULL,
	CREATED_DATE		DATETIME NOT NULL,
	MODIFIED_BY			NVARCHAR(100) NOT NULL,
	MODIFIED_DATE		DATETIME NOT NULL,
	CONSTRAINT PK_RPS_TASKS PRIMARY KEY (TASK_ID)
	)
GO

CREATE INDEX IX_RPS_TASKS
	ON RPS_TASKS (TASK_NAME)
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASKS_AUDIT
	(
	TASK_ID				INT NOT NULL,
	TASK_NAME			NVARCHAR (50) NOT NULL,
	WORKFLOW_NAME		NVARCHAR (50) NOT NULL,
	TASK_DESC			NVARCHAR (2500),
	TASK_GRP_ID			NVARCHAR (50) NOT NULL,
	CMD_PARAMETERS		NVARCHAR(2500),
	NO_OF_RETRY			INT,
	SLEEP_BETWEEN_RETRY INT,
	[TIMEOUT]			INT,
	DELETED	      		BIT NOT NULL,
	MACHINE_NAME		NVARCHAR(50) NOT NULL,
	CREATED_BY			NVARCHAR(100) NOT NULL,
	CREATED_DATE		DATETIME NOT NULL,
	MODIFIED_BY			NVARCHAR(100) NOT NULL,
	MODIFIED_DATE		DATETIME NOT NULL,
	[VERSION]			INT NOT NULL,
	CONSTRAINT PK_RPS_TASKS_AUDIT PRIMARY KEY (TASK_ID, [VERSION])
	)
GO

------------------------------------------------------------------------


CREATE TABLE RPS_TASK_BATCH
	(
	BATCH_ID   	  INT NOT NULL,
	TASK_ID	  	  INT NOT NULL,
	SCHEDULE_ID	  INT NOT NULL,
	TASK_STATUS	  INT NOT NULL,
	CMD_PARAMETERS NVARCHAR(2500),
	LOG_MSG		  NVARCHAR(2500),
	START_TIME	  DATETIME NOT NULL,
	END_TIME      DATETIME,
	DURATION      NVARCHAR(2500),
	NEXT_RUN_TIME DATETIME NULL,
	EXIT_CODE	  INT NOT NULL DEFAULT(0),
	RETRY_COUNT	  INT NOT NULL DEFAULT(0),
	MODIFIED_BY	  NVARCHAR(100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	CONSTRAINT PK_RPS_TASK_BATCH PRIMARY KEY (BATCH_ID, TASK_ID, SCHEDULE_ID)
	)
GO

----------------------------------------------------------------

CREATE TABLE RPS_TASK_BATCH_AUDIT
	(
	BATCH_ID   	  INT NOT NULL,
	TASK_ID	  	  INT NOT NULL,
	SCHEDULE_ID	  INT NOT NULL,
	TASK_STATUS	  INT NOT NULL,
	CMD_PARAMETERS NVARCHAR(2500),
	LOG_MSG		  NVARCHAR(2500),
	START_TIME	  DATETIME NOT NULL,
	END_TIME      DATETIME,
	DURATION      NVARCHAR(2500),
	NEXT_RUN_TIME DATETIME NULL,
	EXIT_CODE	  INT NOT NULL DEFAULT(0),
	RETRY_COUNT	  INT NOT NULL DEFAULT(0),
	MODIFIED_BY	  NVARCHAR(100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	CONSTRAINT PK_RPS_TASK_BATCH_AUDIT PRIMARY KEY (BATCH_ID, TASK_ID, SCHEDULE_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------


CREATE TABLE RPS_TASK_SCHEDULES
	(
	SCHEDULE_ID					INT IDENTITY NOT NULL,
	SCHEDULE_NAME				NVARCHAR(50) NOT NULL,
	SCHEDULE_DESC				NVARCHAR(2500),
	TASK_ID	  					INT NOT NULL,
	[ENABLED]					BIT NOT NULL  DEFAULT(1),
	[START_DATE]				DATETIME NOT NULL,
	[END_DATE]					DATETIME,
	START_TIME					DATETIME NOT NULL,
	END_TIME					DATETIME,
	HOLIDAY_CAL_ID				INT,
	SCHEDULE_TYPE_ID			INT,
	T1							INT,
	DAILY_EVERY_DAYS			INT,
	WEEKLY_EVERY_WEEKS			INT,
	SUN							BIT NOT NULL DEFAULT(0),
	MON							BIT NOT NULL DEFAULT(0),
	TUE							BIT NOT NULL DEFAULT(0),
	WED							BIT NOT NULL DEFAULT(0),
	THU							BIT NOT NULL DEFAULT(0),
	FRI							BIT NOT NULL DEFAULT(0),
	SAT							BIT NOT NULL DEFAULT(0),
	MONTHLY_DAY_OF_MONTH		BIT,
	MONTHLY_EVERY_DAY_OF_MONTH	INT,
	MONTHLY_EVERY_WEEK_OF_MONTH	INT,
	MONTHLY_EVERY_WEEKDAY_OF_MONTH	INT,
	MONTHLY_SELECTED_MONTHS		NCHAR(12),
	REPEAT_ENABLED				BIT NOT NULL DEFAULT(0),
	REPEAT_INTERVAL				INT,
	REPEAT_INTERVAL_TYPE		INT,
	DEPENDENCIES				NVARCHAR(2500),
	FIRE_AFTER_DEPENDENCY		INT NOT NULL DEFAULT(0),
	CREATED_BY					NVARCHAR(100) NOT NULL,
	CREATED_DATE				DATETIME NOT NULL,
	MODIFIED_BY					NVARCHAR(100) NOT NULL,
	MODIFIED_DATE				DATETIME NOT NULL,
	CONSTRAINT PK_RPS_TASK_SCHEDULES PRIMARY KEY (SCHEDULE_ID, TASK_ID),
    CONSTRAINT [UQ_RPS_TASK_SCHEDULES] UNIQUE (SCHEDULE_ID, SCHEDULE_NAME)
	)
GO

----------------------------------------------------------------


CREATE TABLE RPS_TASK_SCHEDULES_AUDIT
	(
	SCHEDULE_ID					INT NOT NULL,
	SCHEDULE_NAME				NVARCHAR(50) NOT NULL,
	SCHEDULE_DESC				NVARCHAR(2500),
	TASK_ID	  					INT NOT NULL,
	[ENABLED]					BIT NOT NULL  DEFAULT(1),
	[START_DATE]				DATETIME NOT NULL,
	[END_DATE]					DATETIME,
	START_TIME					DATETIME NOT NULL,
	END_TIME					DATETIME,
	HOLIDAY_CAL_ID				INT,
	SCHEDULE_TYPE_ID			INT,
	T1							INT,
	DAILY_EVERY_DAYS			INT,
	WEEKLY_EVERY_WEEKS			INT,
	SUN							BIT NOT NULL DEFAULT(0),
	MON							BIT NOT NULL DEFAULT(0),
	TUE							BIT NOT NULL DEFAULT(0),
	WED							BIT NOT NULL DEFAULT(0),
	THU							BIT NOT NULL DEFAULT(0),
	FRI							BIT NOT NULL DEFAULT(0),
	SAT							BIT NOT NULL DEFAULT(0),
	MONTHLY_DAY_OF_MONTH		BIT,
	MONTHLY_EVERY_DAY_OF_MONTH	INT,
	MONTHLY_EVERY_WEEK_OF_MONTH	INT,
	MONTHLY_EVERY_WEEKDAY_OF_MONTH	INT,
	MONTHLY_SELECTED_MONTHS		NCHAR(12),
	REPEAT_ENABLED				BIT NOT NULL DEFAULT(0),
	REPEAT_INTERVAL				INT,
	REPEAT_INTERVAL_TYPE		INT,
	DEPENDENCIES				NVARCHAR(2500),
	FIRE_AFTER_DEPENDENCY		INT NOT NULL DEFAULT(0),
	CREATED_BY					NVARCHAR(100) NOT NULL,
	CREATED_DATE				DATETIME NOT NULL,
	MODIFIED_BY					NVARCHAR(100) NOT NULL,
	MODIFIED_DATE				DATETIME NOT NULL,
	CONSTRAINT PK_RPS_TASK_SCHEDULES_AUDIT PRIMARY KEY (SCHEDULE_ID, TASK_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

